package com.servicesDirectory.dao.organisation.team;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.servicesDirectory.common.exception.FunctionalException;
import com.servicesDirectory.common.exception.SystemException;
import com.servicesDirectory.common.vo.organisation.DirectorateVO;
import com.servicesDirectory.common.vo.organisation.TeamVO;
import com.servicesDirectory.dao.BaseDAO;

public class TeamDAOImpl implements TeamDAO, BaseDAO {
	Connection conn = null;
	ArrayList<TeamVO> listTeam = new ArrayList<TeamVO>();
	private String teamName;

	//connection to database method
	public void initConnection() {
		if(conn == null)
		{
			try {
				Class.forName(JDBC_DRIVER);
				conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);

			} catch (Exception e) {
				e.getStackTrace();
			}
		}
		else{
			conn = null;
			try {
				Class.forName(JDBC_DRIVER);
				conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);

			} catch (Exception e) {
				e.getStackTrace();
			}	
		}
	}

	@Override
	public boolean addTeam(TeamVO vo) throws FunctionalException,
			SystemException {
		
		boolean checkAdd = false;
			initConnection();
			
			try {
				Statement st = conn.createStatement();
				int intIsActive ;
				
				String qr = "INSERT INTO Team (TeamName,[ShortDesc],[ContactID],[CopyAddr],[Addr1],[Addr2],[Addr3],[Postcode],[TownName],[CountyName],[CountryName],[TypeOfBusinessID],[FullDesc],[PhoneNumber],[Fax],[Email],[WebAddr],[DepartmentID],[IsActive]) VALUES "
						+ "('"+vo.getTeamName()+"','"+vo.getShortDesc()+"','"+vo.getContactID()+"',"+1+",'"+vo.getAddr1()+"','" 
						+vo.getAddr2()+"','"+vo.getAddr3()+"','"+vo.getPostcode()+"','"+vo.getTownName()+"','"+vo.getCountyName()+"','"+vo.getCountryName()+"',"+vo.getTypeOfBusinessID()+",'"+vo.getFullDesc()+"','"+vo.getPhoneNumber()+"','"+vo.getFax()+"','"+vo.getEmail()+"','"+vo.getWebAddr()+"',"+3+","+1+")";
				

				

				
				 st.executeUpdate(qr);
				checkAdd = true;
				return checkAdd;
				
			}catch (Exception e) {
				checkAdd = false;
				
				e.printStackTrace();
				return checkAdd;
			}
			
	}
	@Override
	public boolean amendTeam(TeamVO vo) throws FunctionalException,
			SystemException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public List<TeamVO> listTeam(int departmentID, String filter, boolean isInclude)
			throws FunctionalException, SystemException {
		// TODO Auto-generated method stub
		String qr = null;
		Statement st = null;
		TeamVO team = null;
		String qrNotInclude = " AND Team.IsActive = 1";
		ArrayList<TeamVO> listTeam = new ArrayList<TeamVO>();
		try {
			initConnection();
			
			st = conn.createStatement();

			if (isInclude == true) {
				if (filter.equals("ABCDE")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'A%' "
							+ "OR Team.TeamName LIKE 'B%' "
							+ "OR Team.TeamName LIKE 'C%' "
							+ "OR Team.TeamName LIKE 'D%' "
							+ "OR Team.TeamName LIKE 'E%' ";
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'F%' "
							+ "OR Team.TeamName LIKE 'G%' "
							+ "OR Team.TeamName LIKE 'H%' "
							+ "OR Team.TeamName LIKE 'J%' "
							+ "OR Team.TeamName LIKE 'I%' ";
				} else if (filter.equals("KLMN")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'K%' "
							+ "OR Team.TeamName LIKE 'L%' "
							+ "OR Team.TeamName LIKE 'M%' "
							+ "OR Team.TeamName LIKE 'N%' ";
				} else if (filter.equals("OPQR")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'O%' "
							+ "OR Team.TeamName LIKE 'P%' "
							+ "OR Team.TeamName LIKE 'Q%' "
							+ "OR Team.TeamName LIKE 'R%' ";
				} else if (filter.equals("STUV")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'S%' "
							+ "OR Team.TeamName LIKE 'T%' "
							+ "OR Team.TeamName LIKE 'U%' "
							+ "OR Team.TeamName LIKE 'V%' ";
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'W%' "
							+ "OR Team.TeamName LIKE 'X%' "
							+ "OR Team.TeamName LIKE 'Y%' "
							+ "OR Team.TeamName LIKE 'Z%' ";
				} else if (filter.equals("0-9")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE '0%' "
							+ "OR Team.TeamName LIKE '9%' "
							+ "OR Team.TeamName LIKE '8%' "
							+ "OR Team.TeamName LIKE '7%' "
							+ "OR Team.TeamName LIKE '6%' "
							+ "OR Team.TeamName LIKE '5%' "
							+ "OR Team.TeamName LIKE '4%' "
							+ "OR Team.TeamName LIKE '3%' "
							+ "OR Team.TeamName LIKE '2%' "
							+ "OR Team.TeamName LIKE '1%' ";

				} else {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID";
				}
			} else {
				if (filter.equals("ABCDE")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'A%' "
							+ "OR Team.TeamName LIKE 'B%' "
							+ "OR Team.TeamName LIKE 'C%' "
							+ "OR Team.TeamName LIKE 'D%' "
							+ "OR Team.TeamName LIKE 'E%' "
							+ qrNotInclude;
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'F%' "
							+ "OR Team.TeamName LIKE 'G%' "
							+ "OR Team.TeamName LIKE 'H%' "
							+ "OR Team.TeamName LIKE 'J%' "
							+ "OR Team.TeamName LIKE 'I%'";
				} else if (filter.equals("KLMN")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'K%' "
							+ "OR Team.TeamName LIKE 'L%' "
							+ "OR Team.TeamName LIKE 'M%' "
							+ "OR Team.TeamName LIKE 'N%' " + qrNotInclude;
				} else if (filter.equals("OPQR")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'O%' "
							+ "OR Team.TeamName LIKE 'P%' "
							+ "OR Team.TeamName LIKE 'Q%' "
							+ "OR Team.TeamName LIKE 'R%'" + qrNotInclude;
				} else if (filter.equals("STUV")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'S%' "
							+ "OR Team.TeamName LIKE 'T%' "
							+ "OR Team.TeamName LIKE 'U%' "
							+ "OR Team.TeamName LIKE 'V%' " + qrNotInclude;
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE 'W%' "
							+ "OR Team.TeamName LIKE 'X%' "
							+ "OR Team.TeamName LIKE 'Y%' "
							+ "OR Team.TeamName LIKE 'Z%' " + qrNotInclude;
				} else if (filter.equals("0-9")) {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID WHERE "
							+ " Team.TeamName LIKE '0%' "
							+ "OR Team.TeamName LIKE '9%' "
							+ "OR Team.TeamName LIKE '8%' "
							+ "OR Team.TeamName LIKE '7%' "
							+ "OR Team.TeamName LIKE '6%' "
							+ "OR Team.TeamName LIKE '5%' "
							+ "OR Team.TeamName LIKE '4%' "
							+ "OR Team.TeamName LIKE '3%' "
							+ "OR Team.TeamName LIKE '2%' "
							+ "OR Team.TeamName LIKE '1%' " + qrNotInclude;

				} else {
					qr = "SELECT Team.TeamID, Team.TeamName, Team.Addr1, Team.Postcode, Contact.surName, Contact.firstName, Team.IsActive "
							+ "FROM Team INNER JOIN Contact ON Contact.ContactID=Team.ContactID " + qrNotInclude;
				}
			}
			ResultSet rs = st.executeQuery(qr);
			while (rs.next()) {
				int teamID = rs.getInt(1);
				String teamName = rs.getString(2);
				String addr1 = rs.getString(3);
				String postcode = rs.getString(4);
				String contactName = rs.getString(5) + " " + rs.getString(6);
				int intIsActive = rs.getInt(7);
				team = new TeamVO(teamID, teamName, addr1,
						postcode,  intIsActive, contactName);
				listTeam.add(team);
			}
			return listTeam;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

}
